import abc
import re

import pymysql

from com.cqt.tools.mysql.table_fields import TableField, Table


class DorisDB:

    @abc.abstractmethod
    def get_conn(self):
        pass

    def __init__(self):
        # 替换以下参数为你的数据库实际信息
        self._connection = None
        self._cursor = None

    def fetch_one_sql(self, sql, params=None):
        try:
            self.get_conn()
            self._cursor = self._connection.cursor()
            self._cursor.execute(sql, params or ())
            return self._cursor.fetchone()
        except Exception as e:
            print(e)
            return None
        finally:
            self.close()

    def fetch_all_sql(self, sql, params=None):
        try:
            self.get_conn()
            self._cursor = self._connection.cursor()
            self._cursor.execute(sql, params or ())
            return self._cursor.fetchall()
        except Exception as e:
            print(e)
            return None
        finally:
            self.close()

    def extract_table_name(self, sql_query):
        """
        从简单的SQL查询语句中提取表名
        支持格式: SELECT...FROM table_name...
        """
        pattern = r'from\s+([a-zA-Z_][a-zA-Z0-9_]*)'
        match = re.search(pattern, sql_query, re.IGNORECASE)
        if match:
            return match.group(1)
        return None

    def fetch_all(self, sql, params=None):
        try:
            self.get_conn()
            self._cursor = self._connection.cursor()
            self._cursor.execute(sql, params or ())
            rs = self._cursor.fetchall()
            table_name = self.extract_table_name(sql)
            if table_name is not None:
                rs_maps=[]
                t = self.desc_table(tableName=table_name)
                for r in rs:
                    rs_map={}
                    rs_maps.append(rs_map)
                    i =0
                    for tf in t.tfs:
                        rs_map[tf.javaName]=r[i]
                        i+=1
                return rs_maps



            else:
                raise Exception("获取sql查询表名称失败:" + sql)

        except Exception as e:
            print(e)
            return None
        finally:
            self.close()

    def show_tables(self, sql="show tables"):
        rs = self.fetch_all_sql(sql)
        if rs is not None:
            tables = []
            for r in rs:
                tables.append(r[0])
            return tables
        return rs

    def desc_table(self, tableName):
        rs = self.fetch_all_sql("SHOW FULL COLUMNS FROM  " + tableName)
        if rs is not None:
            tables = []
            for r in rs:
                f = TableField()
                tables.append(f.build(r))
            table = Table(tables)
            rs = self.fetch_all_sql("SHOW create table   " + tableName)
            table.build(rs)
            return table
        return rs

    def close(self):
        """安全关闭数据库连接"""
        if self._cursor:
            self._cursor.close()
            self._cursor = None
        if self._connection:
            self._connection.close()
            self._connection = None

    def build_insert_sql(self,table_name,obj):

        columns = self.desc_table(tableName=table_name).tfs
        ks=[]
        for column in columns:
            ks.append(
            {
                'name':column.name,
                'jdbcType':column.jdbcType,
                'javaName':column.javaName
            })
        kfs=[]
        for k in ks:
            kfs.append("'{}'".format(k['name']))


        sb=" INSERT INTO "+table_name+"("
        sb+=",".join(kfs)
        sb+=") VALUES ( "
        vs=[]
        for k in ks:
            v = obj[k['javaName']]
            if 'char' in str( k['jdbcType']).lower():
                vs.append("'{}'".format(v))
            elif 'decimal'  in str( k['jdbcType']).lower():
                vs.append("{}".format(v))
            else:
                vs.append("{}".format(v))
        sb+= ",".join(vs)
        sb+=")"
        return sb




class dev_doris_db(DorisDB):

    def get_conn(self):
        '''
                doris: # Doris数据源
              url: jdbc:mysql://10.255.53.29:9030/boss_platform_charge_cdr?useSSL=false&serverTimezone=Asia/Shanghai
              username: cqt
              password: cqt@2020
        '''
        self._connection = pymysql.connect(host="10.255.53.29",  # 数据库地址
                                           port=9030,
                                           user="cqt",  # 用户名
                                           password="cqt@2020",  # 密码
                                           database="boss_platform_charge_cdr",  # 数据库名
                                           charset='utf8mb4')  # 字符集，根据需要选择
